# -*- coding: utf-8 -*-
import socket
import json
import subprocess
import time
from playwright.sync_api import sync_playwright
from flask import Flask, request, render_template, Response, jsonify, send_from_directory
import duckdb
from database.duckdb import duckdb_base
import threading
import os
from service.rpa import rpa_run
from config import ini_op
from flask_cors import CORS
import sys, uuid
import pandas as pd
import tkinter as tk
from tkinter import filedialog
from time import strftime
from database.duckdb.duckdb_base import get_connection

from controller.nw_fymc.nw_fymc_controller import nw_fymc_blueprint  # 导入 Blueprint
from controller.nw_fymc.nw_fymc_mx_controller import nw_fymc_mx_blueprint
from controller.nw_project.nw_project_controller import nw_project_blueprint
from controller.nw_project.nw_project_hsm_controller import nw_project_hsm_blueprint
from controller.nw_szy.nw_szy_controller import nw_szy_blueprint
from controller.nw_szy.nw_szy_mx_controller import nw_szy_mx_blueprint
from controller.nw_pz.nw_pz_controller import nw_pz_blueprint
from service.data_upload.file_upload import file_upload_blueprint
from utils.path_utils import get_project_root

from controller.robot.rpa_pzsc_controller import rpa_pzsc_blueprint
from controller.robot.rpa_kmzdsc_controller import rpa_kmzdsc_blueprint
from controller.robot.rpa_kmyebsc_controller import rpa_kmyebsc_blueprint
from controller.robot.rpa_xmyebsc_controller import rpa_xmyebsc_blueprint
from controller.robot.rpa_wldwsc_controller import rpa_wldwsc_blueprint
from controller.robot.rpa_bmzdsc_controller import rpa_bmzdsc_blueprint
from controller.robot.rpa_xmzdsc_controller import rpa_xmzdsc_blueprint

from controller.SQLSpace.work_sql_controller import database

from controller.u8.u8_update_controller import u8_update_controller

app = Flask(__name__, static_folder='static', template_folder='templates')
# 允许跨域传输数据
CORS(app)

# 注册 Blueprint
app.register_blueprint(nw_fymc_blueprint, url_prefix='/nw_fymc')
app.register_blueprint(nw_fymc_mx_blueprint, url_prefix='/nw_fymc_mx')
app.register_blueprint(nw_project_blueprint, url_prefix='/nw_project')
app.register_blueprint(nw_project_hsm_blueprint, url_prefix='/nw_project_hsm')
app.register_blueprint(nw_szy_blueprint, url_prefix='/nw_szy')
app.register_blueprint(nw_szy_mx_blueprint, url_prefix='/nw_szy_mx')
app.register_blueprint(nw_pz_blueprint, url_prefix='/nw_pz')
app.register_blueprint(file_upload_blueprint, url_prefix='/file_upload')

app.register_blueprint(rpa_pzsc_blueprint, url_prefix='/rpa')
app.register_blueprint(rpa_kmzdsc_blueprint, url_prefix='/rpa')
app.register_blueprint(rpa_kmyebsc_blueprint, url_prefix='/rpa')
app.register_blueprint(rpa_xmyebsc_blueprint, url_prefix='/rpa')
app.register_blueprint(rpa_wldwsc_blueprint, url_prefix='/rpa')
app.register_blueprint(rpa_bmzdsc_blueprint, url_prefix='/rpa')
app.register_blueprint(rpa_xmzdsc_blueprint, url_prefix='/rpa')

app.register_blueprint(database, url_prefix='/sql')

app.register_blueprint(u8_update_controller, url_prefix='/u8')

BASE_DIR = os.path.dirname(os.path.realpath(sys.argv[0]))


# 处理 React 前端路由（所有其他路由返回 React 应用）
@app.route('/robot', defaults={'path': ''})
@app.route('/robot/<path:path>')
def serve_react_app(path):
    page_path = os.path.join(app.static_folder, 'page', 'static')
    if path != "" and os.path.exists(os.path.join(page_path, path)):
        return send_from_directory(page_path, path)
    else:
        return send_from_directory(page_path, 'index.html')

@app.route('/work', defaults={'path': ''})
@app.route('/work/<path:path>')
def serve_sql_app(path):
    work_path = os.path.join(app.static_folder, 'work', 'static')
    if path != "" and os.path.exists(os.path.join(work_path, path)):
        return send_from_directory(work_path, path)
    else:
        return send_from_directory(work_path, 'index.html')



@app.route('/execute/initsql')
def execute_initsql():
    databasetype = request.args.get('databasetype')
    if databasetype:
        ini_op.opinivalue('basic_config', **{'databasetype':databasetype})
    try:
        duckdb_base.get_databasetype()
        ret = duckdb_base.init_database_data()
        if ret == 'SQL executed':
            return jsonify({"code": 200, "message": "初始化完成"}), 200
        else:
            return jsonify({"code": 501, "message": "初始化失败"}), 500
    except Exception as e:
        return jsonify({"code": 500, "error": str(e)}), 500

@app.route('/rpa/dict/search', methods=['POST'])
def dict_search():
    data = request.json
    table = data['table']
    filters = data.get('filters', {})
    page = data.get('page', 1)
    page_size = data.get('pageSize', 10)

    # 构造 WHERE 子句
    where_clause = ' AND '.join([
        f"CAST({k} AS VARCHAR) LIKE '%{v}%'"
        for k, v in filters.items() if v
    ])
    query = f"SELECT * FROM {table}"
    if where_clause:
        query += f" WHERE {where_clause}"

    # 统计总数
    count_query = f"SELECT COUNT(*) FROM ({query}) AS sub"

    # 添加分页
    query += f" ORDER BY esun_api_update DESC LIMIT {page_size} OFFSET {(page - 1) * page_size}"
    print(query)
    con = get_connection()
    results = con.execute(query).fetchdf()
    # 处理 NaT、NaN等
    results = results.applymap(lambda x: None if pd.isna(x) else x)
    for col in results.select_dtypes(include=['datetime64[ns]']):
        results[col] = results[col].astype(str)
    # results = results.where(pd.notnull(results), None)
    # print(results)
    # 获取总条数
    total = con.execute(count_query).fetchone()[0]
    con.close()

    return jsonify({
        'columns': list(results.columns),
        'data': results.to_dict(orient='records'),
        'total': total
    })




# 系统配置
@app.route('/sys/get_sys_config', methods=['POST'])
def get_sys_config():
    data = request.json
    l_config = data.get('config')
    data = ini_op.getjson('basic_config' if l_config is None else l_config)
    # 返回JSON响应
    data = json.dumps(data, ensure_ascii=False)
    return Response(data, content_type="application/json")


# 系统配置
@app.route('/rpa/config', methods=['GET', 'POST'])
def rpa_config():
    data = ini_op.getjson('basic_config')
    # 返回JSON响应
    data = json.dumps(data, ensure_ascii=False)
    return Response(data, content_type="application/json")


# 更新系统配置
@app.route('/rpa/config/update', methods=['GET', 'POST'])
def rpa_config_update():
    data = request.json
    ini_op.opinivalue('basic_config', **data)
    # 返回JSON响应
    data = json.dumps({"message": "success"}, ensure_ascii=False)
    return Response(data, content_type="application/json")


# 选取文件
@app.route('/select-file', methods=['GET', 'POST'])
def select_file():
    data = request.json
    select_type = data['type']
    try:
        filetypes = data['filetype']
    except Exception:
        filetypes = ['xlsx', 'xls']
    try:
        path = data['path']
    except Exception:
        path = ''
    if select_type == 'file':
        if path == '':
            pathdir = ''
        else:
            pathdir = os.path.dirname(path)
    else:
        pathdir = path
    selected_folder = ''
    # 创建一个简单的tkinter窗口
    root = tk.Tk()
    root.withdraw()  # 隐藏主窗口
    root.attributes("-topmost", True)
    if select_type == 'file':
        # 定义文件类型
        file_types = []
        for filetype in filetypes:
            file_types.append(('.' + filetype, '*.' + filetype))

        # 使用filedialog打开文件选择对话框，并指定文件类型
        if pathdir == '':
            file_path = filedialog.askopenfilename(
                title="选择文件",
                filetypes=file_types  # 指定文件类型
            )
        else:
            file_path = filedialog.askopenfilename(
                title="选择文件",
                initialdir=pathdir,  # 初始目录
                filetypes=file_types  # 指定文件类型
            )
        # 将文件路径存储在变量中
        if file_path:
            selected_folder = file_path

    if select_type == 'folder':
        if pathdir == '':
            folder_path = filedialog.askdirectory()
        else:
            folder_path = filedialog.askdirectory(
                title="选择文件夹",
                initialdir=pathdir
            )
        # 将文件夹路径存储在变量中
        if folder_path:
            selected_folder = folder_path

    root.destroy()

    if selected_folder == '':
        data = {
            'status': 'success',
            'path': path
        }
    else:
        data = {
            'status': 'success',
            'path': selected_folder
        }
    # data = json.dumps(data, ensure_ascii=False)
    return data


@app.route('/execute', methods=['POST'])
def execute():
    # 接收名为SQL的body参数
    data = request.json
    sql = data.get('sql')
    # print(sql)
    return duckdb_base.execute(sql)


@app.route('/showtables', methods=['GET', 'POST'])
def show_tables():
    tables = duckdb_base.show_tables()
    # print(tables)
    return render_template('dbsql.html', tables=tables)


@app.route('/query', methods=['GET', 'POST'])
def query():
    if request.method == 'POST':
        sql = request.form['sql']
        print(sql)
        try:
            result = duckdb_base.query(sql)
            return render_template('query.html', sql=sql, result=result, error=None)
        except Exception as e:
            return render_template('query.html', sql=sql, result=None, error=str(e))
    return render_template('query.html', sql='', result=None, error=None)


@app.route('/table/<name>')
def table(name):
    columns, data = duckdb_base.table(name)
    return render_template('table.html', table_name=name, columns=columns, data=data)


@app.route('/readexcel')
def readexcel():
    # 测试方法
    # 读取 Excel 文件

    BASE_DIR = os.path.dirname(os.path.abspath(__file__))
    file_path = os.path.join(BASE_DIR, "file", "凭证_数据导出1.xlsm")
    df = pd.read_excel(file_path, sheet_name='凭证头', engine='openpyxl')
    print(df.columns.tolist())
    print(df.values.tolist())
    return show_tables()


# 浏览器启动函数
#若已启动则取消再次运行
def check_port_in_use(port):
    with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as s:
        return s.connect_ex(('127.0.0.1', port)) != 0
def open_browser1(app_port, chrome_port):
    BASE_DIR = os.path.dirname(os.path.realpath(sys.argv[0]))
    exe_path = r'playwright\driver\package\.local-browsers\chromium-920619\chrome-win\chrome.exe'
    browser_path = os.path.join(BASE_DIR, exe_path)
    url = 'http://127.0.0.1:'+app_port+'/robot'
    command = [
        browser_path,
        f'--remote-debugging-port={chrome_port}',
        '--start-maximized',
        url,
    ]
    browser_process = subprocess.Popen(command)
    # 监控浏览器进程
    while True:
        # 检查进程是否仍在运行
        retcode = browser_process.poll()
        if retcode is not None:
            # 如果进程已结束，调用 os._exit(0) 终止程序
            os._exit(0)
        time.sleep(1)

def open_browser(app_port, chrome_port):
    with sync_playwright() as p:
        # 设置启动参数
        args = [
            '--start-maximized',
            f'--remote-debugging-port={chrome_port}'
        ]
        browser = p.chromium.launch_persistent_context(chrome_port, headless=False, slow_mo=50, no_viewport=True, args=args)
        try:
            page = browser.pages[0]
        except Exception:
            page = browser.new_page()
        page.goto('http://127.0.0.1:'+app_port+'/robot')
        try:
            page.wait_for_selector('#cjdshsjkskfsk', timeout=300000000)
        except Exception:
            browser.close()
        finally:
            os._exit(0)


if __name__ == '__main__':
    app_port, chrome_port, browser_select = ini_op.getinivalue('app_config', 'app_port', 'chrome_port', 'browser_select')
    # 仅在主进程中启动浏览器线程
    # if check_port_in_use(int(app_port)):
    #     if browser_select == '2':
    #         browser_thread = threading.Thread(target=open_browser1, args=[app_port, chrome_port])
    #     else:
    #         browser_thread = threading.Thread(target=open_browser, args=[app_port, chrome_port])
    #     browser_thread.start()
    app.run(host='0.0.0.0',port=app_port, debug=True)
